#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : copyfile.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/3/26 14:26                 #
#                                                                   #
#                   @Last Update  : 2021/3/26 14:26                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:导入印尼专家提供的数据                                                          #
#                                                                   #
#-------------------------------------------------------------------#
"""
import hashlib
import os
import shutil

import pandas as pd
import pymysql
import redis

from Seetings import CJMYSQL_CONNECT, REDIS_CONNECT


class Sample:
    def __init__(self):
        self.__FILE_PATH = r"D:\GlobalLawFiles"
        self.__table = "`law`"
        self.__cjconn = pymysql.connect(host=CJMYSQL_CONNECT['MYSQL_HOST'], port=CJMYSQL_CONNECT['MYSQL_PORT'],
                                      db=CJMYSQL_CONNECT['MYSQL_DB'], user=CJMYSQL_CONNECT['MYSQL_USER'],
                                      passwd=CJMYSQL_CONNECT['MYSQL_PASSWD'], charset='utf8')
        self.__cjcour = self.__cjconn.cursor()
        # 连接redis数据库
        self.__Reidsconn = redis.ConnectionPool(host=REDIS_CONNECT['REDIS_HOST'], port=REDIS_CONNECT['REDIS_PORT'],
                                              db=REDIS_CONNECT['REDIS_DB'], password=REDIS_CONNECT['REDIS_PASSWD'],
                                              decode_responses=True)

    def __del__(self):
        self.__cjcour.close()
        self.__cjconn.close()

    def dealstr(self, strdata):
        if strdata == 'nan':
            strdata = ""
        return strdata

    def addinsql(self, datadict):
        """
        将一条记录加入sql数据库
        :param datadict:一个dict格式的记录
        :return:
        """
        # 数据存入数据库
        sql = '''insert into %s (LegalName,Organizaation,PublishDate,EffectiveDate,SortA,SortB,SortC, 
                SORTF,SORTG,ChapNo,DownLoadWebNameC, SYS_FLD_DIGITFILENAME,FileUrl,SYSID, Website, Isconversion) 
                values("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")''' \
              % (self.__table, pymysql.escape_string(datadict["LegalName"]), datadict["Organizaation"],datadict["PublishDate"],datadict["EffectiveDate"],
                 datadict["SortA"],datadict["SortB"],datadict["SortC"],datadict["SORTF"],datadict["SORTG"],
                 datadict["ChapNo"],datadict["DownLoadWebNameC"],datadict["SYS_FLD_DIGITFILENAME"],datadict["FileUrl"],
                 datadict["SYSID"],datadict["Website"],datadict["Isconversion"])
        try:
            self.__cjcour.execute(sql)
            self.__cjconn.commit()
            print('网站数据存入数据库成功')
            # redisaddcursor = redis.Redis(connection_pool=self.__Reidsconn)
            # try:
            #     redisaddcursor.sadd(REDIS_CONNECT['REDIS_KEY'], datadict["SYSID"])
            #     redisaddcursor.close()
            # except Exception as e:
            #     print('网站id存入redis数据库异常:' + str(e))
            #     sql = "DELETE FROM %s WHERE SYSID = '%s'" % (self.__table, datadict["SYSID"])
            #     try:
            #         self.__cjcour.execute(sql)
            #     except Exception as e:
            #         print('mysql删除数据异常：' + str(e))
            #     self.__cjconn.commit()
        except Exception as e:
            with open("sqlerro.txt", "a", encoding="utf-8") as g:
                g.write(sql + "\n")
            print('网站数据存入mysql数据库异常:' + str(e))


    def importdata(self, excel_path, sheet_name, file_path, country, language, pdf_path, website):
        j = 0
        data = pd.read_excel(excel_path, sheet_name)
        for i in range(1, len(data)):
            value = str(data.iloc[i, 10])
            if value == 'nan':
                continue
            Isright = False
            # pdf_name = value + '.pdf'
            # pdf_name1 = value.rstrip() + '.pdf'
            pdf_name = value
            for root, dirs, files in os.walk(file_path, topdown=False):
                if pdf_name in files:
                    items = {}
                    Isright = True
                    SYS_FLD_DIGITFILENAME = 'f' + str(hashlib.md5(value.encode('utf-8')).hexdigest()) +".pdf"
                    items["LegalName"] = self.dealstr(str(data.iloc[i,1]))
                    items["Organizaation"] = self.dealstr(str(data.iloc[i,2]))
                    items["PublishDate"] = self.dealstr(str(data.iloc[i,3]))
                    items["EffectiveDate"] = self.dealstr(str(data.iloc[i,4]))
                    items["SortA"] = country
                    items["SortB"] = language
                    items["SortC"] = self.dealstr(str(data.iloc[i,6]))
                    items["SORTF"] = self.dealstr(str(data.iloc[i,7]))
                    items["SORTG"] = self.dealstr(str(data.iloc[i,8]))
                    items["ChapNo"] = self.dealstr(str(data.iloc[i,9]))
                    items["Website"] = website
                    items["DownLoadWebNameC"] = "资源合作"
                    items["FileUrl"] = "/".join([pdf_path, SYS_FLD_DIGITFILENAME])
                    items["SYS_FLD_DIGITFILENAME"] = SYS_FLD_DIGITFILENAME
                    items["SYSID"] = str(hashlib.md5(value.encode('utf-8')).hexdigest())
                    items["Isconversion"] = "0"
                    self.addinsql(items)
                    # 将文件复制到新目录下
                    newpath = os.path.join(self.__FILE_PATH, pdf_path)   # 新目录
                    if not os.path.exists(newpath):
                        os.makedirs(newpath)
                    path_old = os.path.join(root, pdf_name)
                    path_new = os.path.join(self.__FILE_PATH, pdf_path, SYS_FLD_DIGITFILENAME)
                    try:
                        shutil.copyfile(path_old, path_new)
                    except Exception as e:
                        with open("erro.txt", "a", encoding="utf-8") as f:
                            f.write(pdf_name + "\n")
                        print(str(e))
                    break
            if not Isright:
                with open("nopdf.txt", 'a', encoding='utf-8') as f:
                    j = j + 1
                    f.write(str(j) + ':' + str(data.iloc[i, 0]) + ';' + pdf_name + '\n')


if __name__ == '__main__':
    path = r'E:\工作记录\工作内容\“一带一路”项目\各国专家提供的法律资源资料\印度尼西亚\印尼数据'
    excelpath = path + '\Kumpulan UU RI\Indonesian law bibliography data fields.xls'
    sheetname = 'example'
    filepath = path + '\Kumpulan UU RI'
    country = "LAWCOUNTRYYDNXY"
    language = "LANGUAGEYNIY"
    pdf_path = '/PDF/Law/Indonesia/peraturan/undang'
    website = 'Undang-Undang'
    # Sample().checkfile(excelpath,sheetname,filepath)
    Sample().importdata(excelpath, sheetname, filepath, country, language, pdf_path, website)